using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    public class MarksDAO : BaseDAO
    {
        public MarksDAO()
        {
        }

        private Entities.Marks CreateMarksFromReader(IDataReader reader)
        {
            Entities.Marks marks = new Entities.Marks();
            marks.StudentID = (string)reader["cStudentID"];
            marks.Mark = (int)reader["iMark"];
            return marks;
        }

        public void InsertMarks(Entities.Marks marks)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertMarks", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", marks.StudentID));
                command.Parameters.Add(new SqlParameter("@iMark", marks.Mark));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void UpdateMarks(Entities.Marks marks)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateMarks", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", marks.StudentID));
                command.Parameters.Add(new SqlParameter("@iMark", marks.Mark));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeleteMarks(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteMarks", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", sStudentID));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public Entities.Marks GetMarksByStudentID(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetMarksByStudentID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cStudentID", sStudentID));

                connection.Open();
                Entities.Marks marks = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        marks = CreateMarksFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return marks;
            }
        }

        public List<Entities.Marks> GetAllMarks()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllCourses", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Marks> marks = new List<Entities.Marks>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        marks.Add(CreateMarksFromReader(reader));
                    }
                }
                return marks;
            }
        }

        public DataTable GetAllMarks2()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllMarks2", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetMarksByCourseID(string sCourseID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetMarksByCourseID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cCourseID", sCourseID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetMarksByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetMarksByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetMarksByCourseIDAndClassID(string sCourseID, string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetMarksByCourseIDAndClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cCourseID", sCourseID));
                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetMarksByCourseIDAndClassID1(string sCourseID, string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetMarksByCourseIDAndClassID1", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cCourseID", sCourseID));
                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

    }
}
